/**
 * 
 */
package org.swing.utility.text.excel;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ComparatorChain;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellReference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.swing.utility.text.excel.info.ExcelLog;
import org.swing.utility.text.excel.info.ExcelLogs;
import org.swing.utility.text.excel.info.ExcelSheet;
import org.swing.utility.text.excel.info.FieldForSortting;
import org.swing.utility.text.imp.ExcelCell;

/**
 * @author lqnhu
 *
 */
public class ExcelUtil {
	private static Logger LG = LoggerFactory.getLogger(ExcelUtil.class);
	private static Map<Class<?>, Integer[]> validateMap = new HashMap<Class<?>, Integer[]>();
	static {
		validateMap
				.put(String[].class, new Integer[] { Cell.CELL_TYPE_STRING });
		validateMap.put(Double[].class,
				new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap.put(String.class, new Integer[] { Cell.CELL_TYPE_STRING });
		validateMap.put(Double.class, new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap.put(Date.class, new Integer[] { Cell.CELL_TYPE_NUMERIC,
				Cell.CELL_TYPE_STRING });
		validateMap
				.put(Integer.class, new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap.put(Float.class, new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap.put(Long.class, new Integer[] { Cell.CELL_TYPE_NUMERIC });
		validateMap
				.put(Boolean.class, new Integer[] { Cell.CELL_TYPE_BOOLEAN });
	}

	private static String getCellTypeByInt(int cellType) {
		switch (cellType) {
		case Cell.CELL_TYPE_BLANK:
			return "Null type";
		case Cell.CELL_TYPE_BOOLEAN:
			return "Boolean type";
		case Cell.CELL_TYPE_ERROR:
			return "Error type";
		case Cell.CELL_TYPE_FORMULA:
			return "Formula type";
		case Cell.CELL_TYPE_NUMERIC:
			return "Numeric type";
		case Cell.CELL_TYPE_STRING:
			return "String type";
		default:
			return "Unknown type";
		}
	}

	private static Object getCellValue(Cell cell) {
		if (cell == null
				|| (cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils
						.isBlank(cell.getStringCellValue()))) {
			return null;
		}
		int cellType = cell.getCellType();
		switch (cellType) {
		case Cell.CELL_TYPE_BLANK:
			return null;
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();
		case Cell.CELL_TYPE_ERROR:
			return cell.getErrorCellValue();
		case Cell.CELL_TYPE_FORMULA:
			return cell.getNumericCellValue();
		case Cell.CELL_TYPE_NUMERIC:
			return cell.getNumericCellValue();
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		default:
			return null;
		}
	}

	public static <T> void exportExcel(String[] headers, Collection<T> dataset,
			OutputStream out) {
		exportExcel(headers, dataset, out, null);
	}

	public static <T> void exportExcel(String[] headers, Collection<T> dataset,
			OutputStream out, String pattern) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		write2Sheet(sheet, headers, dataset, pattern);
		try {
			workbook.write(out);
		} catch (IOException e) {
			LG.error(e.toString(), e);
		}
	}

	public static <T> void exportExcel(List<ExcelSheet<T>> sheets,
			OutputStream out) {
		exportExcel(sheets, out, null);
	}

	public static <T> void exportExcel(List<ExcelSheet<T>> sheets,
			OutputStream out, String pattern) {
		if (CollectionUtils.isEmpty(sheets)) {
			return;
		}
		HSSFWorkbook workbook = new HSSFWorkbook();
		for (ExcelSheet<T> sheet : sheets) {
			HSSFSheet hssfSheet = workbook.createSheet(sheet.getSheetName());
			write2Sheet(hssfSheet, sheet.getHeaders(), sheet.getDataset(),
					pattern);
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			LG.error(e.toString(), e);
		}
	}

	private static <T> void write2Sheet(HSSFSheet sheet, String[] headers,
			Collection<T> dataset, String pattern) {
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			try {
				if (t instanceof Map) {
					@SuppressWarnings("unchecked")
					Map<String, Object> map = (Map<String, Object>) t;
					int cellNum = 0;
					for (String k : headers) {
						if (map.containsKey(k) == false) {
							LG.error("Map  key [" + k + "]");
							continue;
						}
						Object value = map.get(k);
						HSSFCell cell = row.createCell(cellNum);
						cell.setCellValue(String.valueOf(value));
						cellNum++;
					}
				} else {
					List<FieldForSortting> fields = sortFieldByAnno(t
							.getClass());
					int cellNum = 0;
					for (int i = 0; i < fields.size(); i++) {
						HSSFCell cell = row.createCell(cellNum);
						Field field = fields.get(i).getField();
						field.setAccessible(true);
						Object value = field.get(t);
						String textValue = null;
						if (value instanceof Integer) {
							int intValue = (Integer) value;
							cell.setCellValue(intValue);
						} else if (value instanceof Float) {
							float fValue = (Float) value;
							cell.setCellValue(fValue);
						} else if (value instanceof Double) {
							double dValue = (Double) value;
							cell.setCellValue(dValue);
						} else if (value instanceof Long) {
							long longValue = (Long) value;
							cell.setCellValue(longValue);
						} else if (value instanceof Boolean) {
							boolean bValue = (Boolean) value;
							cell.setCellValue(bValue);
						} else if (value instanceof Date) {
							Date date = (Date) value;
							SimpleDateFormat sdf = new SimpleDateFormat(pattern);
							textValue = sdf.format(date);
						} else if (value instanceof String[]) {
							String[] strArr = (String[]) value;
							for (int j = 0; j < strArr.length; j++) {
								String str = strArr[j];
								cell.setCellValue(str);
								if (j != strArr.length - 1) {
									cellNum++;
									cell = row.createCell(cellNum);
								}
							}
						} else if (value instanceof Double[]) {
							Double[] douArr = (Double[]) value;
							for (int j = 0; j < douArr.length; j++) {
								Double val = douArr[j];
								if (val != null) {
									cell.setCellValue(val);
								}
								if (j != douArr.length - 1) {
									cellNum++;
									cell = row.createCell(cellNum);
								}
							}
						} else {
							String empty = StringUtils.EMPTY;
							ExcelCell anno = field
									.getAnnotation(ExcelCell.class);
							if (anno != null) {
								empty = anno.defaultValue();
							}
							textValue = value == null ? empty : value
									.toString();
						}
						if (textValue != null) {
							HSSFRichTextString richString = new HSSFRichTextString(
									textValue);
							cell.setCellValue(richString);
						}
						cellNum++;
					}
				}
			} catch (Exception e) {
				LG.error(e.toString(), e);
			}
		}
		for (int i = 0; i < headers.length; i++) {
			sheet.autoSizeColumn(i);
		}
	}

	@SuppressWarnings("unchecked")
	public static <T> Collection<T> importExcel(Class<T> clazz,
			InputStream inputStream, String pattern, ExcelLogs logs,
			Integer... arrayCount) {
		HSSFWorkbook workBook = null;
		try {
			workBook = new HSSFWorkbook(inputStream);
		} catch (IOException e) {
			LG.error(e.toString(), e);
		}
		List<T> list = new ArrayList<T>();
		HSSFSheet sheet = workBook.getSheetAt(0);
		Iterator<Row> rowIterator = sheet.rowIterator();
		try {
			List<ExcelLog> logList = new ArrayList<ExcelLog>();
			// Map<title,index>
			Map<String, Integer> titleMap = new HashMap<String, Integer>();
			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();
				if (row.getRowNum() == 0) {
					if (clazz == Map.class) {
						Iterator<Cell> cellIterator = row.cellIterator();
						Integer index = 0;
						while (cellIterator.hasNext()) {
							String value = cellIterator.next()
									.getStringCellValue();
							titleMap.put(value, index);
							index++;
						}
					}
					continue;
				}
				boolean allRowIsNull = true;
				Iterator<Cell> cellIterator = row.cellIterator();
				while (cellIterator.hasNext()) {
					Object cellValue = getCellValue(cellIterator.next());
					if (cellValue != null) {
						allRowIsNull = false;
						break;
					}
				}
				if (allRowIsNull) {
					LG.warn("Excel row " + row.getRowNum()
							+ " all row value is null!");
					continue;
				}
				T t = null;
				StringBuilder log = new StringBuilder();
				if (clazz == Map.class) {
					Map<String, Object> map = new HashMap<String, Object>();
					for (String k : titleMap.keySet()) {
						Integer index = titleMap.get(k);
						String value = row.getCell(index).getStringCellValue();
						map.put(k, value);
					}
					list.add((T) map);
				} else {
					t = clazz.newInstance();
					int arrayIndex = 0;
					int cellIndex = 0;
					List<FieldForSortting> fields = sortFieldByAnno(clazz);
					for (FieldForSortting ffs : fields) {
						Field field = ffs.getField();
						field.setAccessible(true);
						if (field.getType().isArray()) {
							Integer count = arrayCount[arrayIndex];
							Object[] value = null;
							if (field.getType().equals(String[].class)) {
								value = new String[count];
							} else {
								value = new Double[count];
							}
							for (int i = 0; i < count; i++) {
								Cell cell = row.getCell(cellIndex);
								String errMsg = validateCell(cell, field,
										cellIndex);
								if (StringUtils.isBlank(errMsg)) {
									value[i] = getCellValue(cell);
								} else {
									log.append(errMsg);
									log.append(";");
									logs.setHasError(true);
								}
								cellIndex++;
							}
							field.set(t, value);
							arrayIndex++;
						} else {
							Cell cell = row.getCell(cellIndex);
							String errMsg = validateCell(cell, field, cellIndex);
							if (StringUtils.isBlank(errMsg)) {
								Object value = null;
								if (field.getType().equals(Date.class)
										&& cell.getCellType() == Cell.CELL_TYPE_STRING) {
									Object strDate = getCellValue(cell);
									try {
										value = new SimpleDateFormat(pattern)
												.parse(strDate.toString());
									} catch (ParseException e) {
										errMsg = MessageFormat
												.format("the cell [{0}] can not be converted to a date ",
														CellReference
																.convertNumToColString(cell
																		.getColumnIndex()));
									}
								} else {
									value = getCellValue(cell);
									ExcelCell annoCell = field
											.getAnnotation(ExcelCell.class);
									if (value instanceof String
											&& !field.getType().equals(
													String.class)
											&& StringUtils.isNotBlank(annoCell
													.defaultValue())) {
										value = annoCell.defaultValue();
									}
								}
								field.set(t, value);
							}
							if (StringUtils.isNotBlank(errMsg)) {
								log.append(errMsg);
								log.append(";");
								logs.setHasError(true);
							}
							cellIndex++;
						}
					}
					list.add(t);
					logList.add(new ExcelLog(t, log.toString(),
							row.getRowNum() + 1));
				}
			}
			logs.setLogList(logList);
		} catch (InstantiationException e) {
			throw new RuntimeException(MessageFormat.format(
					"can not instance class:{0}", clazz.getSimpleName()), e);
		} catch (IllegalAccessException e) {
			throw new RuntimeException(MessageFormat.format(
					"can not instance class:{0}", clazz.getSimpleName()), e);
		}
		return list;
	}

	private static String validateCell(Cell cell, Field field, int cellNum) {
		String columnName = CellReference.convertNumToColString(cellNum);
		String result = null;
		Integer[] integers = validateMap.get(field.getType());
		if (integers == null) {
			result = MessageFormat.format("Unsupported type [{0}]", field
					.getType().getSimpleName());
			return result;
		}
		ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
		if (cell == null
				|| (cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils
						.isBlank(cell.getStringCellValue()))) {
			if (annoCell != null && annoCell.valid().allowNull() == false) {
				result = MessageFormat.format("the cell [{0}] can not null",
						columnName);
			}
			;
		} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK
				&& annoCell.valid().allowNull()) {
			return result;
		} else {
			List<Integer> cellTypes = Arrays.asList(integers);
			if (!(cellTypes.contains(cell.getCellType()))
					|| StringUtils.isNotBlank(annoCell.defaultValue())
					&& cell.getCellType() == Cell.CELL_TYPE_STRING) {
				StringBuilder strType = new StringBuilder();
				for (int i = 0; i < cellTypes.size(); i++) {
					Integer intType = cellTypes.get(i);
					strType.append(getCellTypeByInt(intType));
					if (i != cellTypes.size() - 1) {
						strType.append(",");
					}
				}
				result = MessageFormat.format("the cell [{0}] type must [{1}]",
						columnName, strType.toString());
			} else {
				// String in
				if (annoCell.valid().in().length != 0
						&& cell.getCellType() == Cell.CELL_TYPE_STRING) {
					String[] in = annoCell.valid().in();
					String cellValue = cell.getStringCellValue();
					boolean isIn = false;
					for (String str : in) {
						if (str.equals(cellValue)) {
							isIn = true;
						}
					}
					if (!isIn) {
						result = MessageFormat.format(
								"the cell [{0}] value must in {1}", columnName,
								in);
					}
				}
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					double cellValue = cell.getNumericCellValue();
					if (!Double.isNaN(annoCell.valid().lt())) {
						if (!(cellValue < annoCell.valid().lt())) {
							result = MessageFormat
									.format("the cell [{0}] value must less than [{1}]",
											columnName, annoCell.valid().lt());
						}
					}
					if (!Double.isNaN(annoCell.valid().gt())) {
						if (!(cellValue > annoCell.valid().gt())) {
							result = MessageFormat
									.format("the cell [{0}] value must greater than [{1}]",
											columnName, annoCell.valid().gt());
						}
					}
					if (!Double.isNaN(annoCell.valid().le())) {
						if (!(cellValue <= annoCell.valid().le())) {
							result = MessageFormat
									.format("the cell [{0}] value must less than or equal [{1}]",
											columnName, annoCell.valid().le());
						}
					}
					if (!Double.isNaN(annoCell.valid().ge())) {
						if (!(cellValue >= annoCell.valid().ge())) {
							result = MessageFormat
									.format("the cell [{0}] value must greater than or equal [{1}]",
											columnName, annoCell.valid().ge());
						}
					}
				}
			}
		}
		return result;
	}

	private static List<FieldForSortting> sortFieldByAnno(Class<?> clazz) {
		Field[] fieldsArr = clazz.getDeclaredFields();
		List<FieldForSortting> fields = new ArrayList<FieldForSortting>();
		List<FieldForSortting> annoNullFields = new ArrayList<FieldForSortting>();
		for (Field field : fieldsArr) {
			ExcelCell ec = field.getAnnotation(ExcelCell.class);
			if (ec == null) {
				continue;
			}
			int id = ec.index();
			fields.add(new FieldForSortting(field, id));
		}
		fields.addAll(annoNullFields);
		sortByProperties(fields, true, false, "index");
		return fields;
	}

	@SuppressWarnings("unchecked")
	private static void sortByProperties(List<? extends Object> list,
			boolean isNullHigh, boolean isReversed, String... props) {
		if (CollectionUtils.isNotEmpty(list)) {
			Comparator<?> typeComp = ComparableComparator.getInstance();
			if (isNullHigh == true) {
				typeComp = ComparatorUtils.nullHighComparator(typeComp);
			} else {
				typeComp = ComparatorUtils.nullLowComparator(typeComp);
			}
			if (isReversed) {
				typeComp = ComparatorUtils.reversedComparator(typeComp);
			}
			List<Object> sortCols = new ArrayList<Object>();
			if (props != null) {
				for (String prop : props) {
					sortCols.add(new BeanComparator(prop, typeComp));
				}
			}
			if (sortCols.size() > 0) {
				Comparator<Object> sortChain = new ComparatorChain(sortCols);
				Collections.sort(list, sortChain);
			}
		}
	}
}
